/*
 * Copyright 2007. Mount Sinai Hospital, Toronto, Canada.
 * 
 * Licensed under the GNU Lesser General Public License, Version 2. You
 * can find a copy of the license at:
 * 
 * http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt
 * 
 * IN NO EVENT SHALL MOUNT SINAI HOSPITAL BE LIABLE TO ANY PARTY FOR DIRECT, 
 * INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST 
 * PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, 
 * EVEN IF MOUNT SINAI HOSPITAL HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH 
 * DAMAGE.
 * 
 * MOUNT SINAI HOSPITAL SPECIFICALLY DISCLAIMS ANY IMPLIED WARRANTIES OF 
 * MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE AND 
 * ACCOMPANYING DOCUMENTATION, IF ANY, PROVIDED HEREUNDER IS PROVIDED "AS IS". 
 * MOUNT SINAI HOSPITAL HAS NO OBLIGATION TO PROVIDE MAINTENANCE, SUPPORT, 
 * UPDATES, ENHANCEMENTS, OR MODIFICATIONS. 
 */
package com.sinai.mshab.server.sql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;

import org.apache.log4j.Logger;

import com.sinai.mshab.client.ui.Key;
import com.sinai.mshab.client.user.LDAPUser;

/**
 * This class provides a convenient way to create safe SQL select queries. This
 * class utilizes {@link PreparedStatement} to insure that user input is made
 * safe. Furthermore, the class automates SQL creation and allows programmers to
 * avoid writing SQL that depends on a single database schema as the SQL query
 * can be made dynamically.
 * 
 * @author Arthur Kalmenson
 */
public class SQLSelect extends SQLObject {

	/**
	 * The {@link PreparedStatement} used in constructing our SQL SELECT.
	 */
	private PreparedStatement select;

	/**
	 * A {@link Set} containing additional tables to pull information from.
	 */
	private Set<String> tables;

	/**
	 * A {@link Map} mapping a {@link DbField} to an alias.
	 */
	private Map<DbField, String> fieldToAlias;

	/**
	 * Creates a new SQLSelect object that utilizes the {@link Connection} conn.
	 * The connection must be instantiated, otherwise an
	 * {@link IllegalArgumentException} is thrown.
	 * 
	 * @param conn
	 *            the connection to the database.
	 */
	public SQLSelect(Connection conn) {
		if (conn == null) {
			throw new IllegalArgumentException("The Connection cannot be null");
		}
		super.connection = conn;
		super.dbFields = new HashSet();
		tables = new HashSet<String>();
		fieldToAlias = new HashMap<DbField, String>();
	}

	/**
	 * Adds table to the tables to select from.
	 * 
	 * @param table
	 *            the name of a table to select from.
	 */
	public void addTable(String table) {

		if (table == null) {
			throw new IllegalArgumentException("Table must be instantiated.");
		} else if (table.trim().isEmpty()) {
			throw new IllegalArgumentException("Table cannot be empty");
		} else {
			tables.add(table);
		}
	}

	/**
	 * Adds a new select field. The select field is the <table name>.<field
	 * name> section that appears in after the "SELECT" keyword. Therefore,
	 * calling addSelectField with "tableA" and "fieldA" would yeild a select
	 * query that would have "SELECT tableA.fieldA ... FROM tableA ...". Note
	 * that the table will automatically appear in the select query. Please note
	 * that all parameters must be instantiated and none empty otherwise an
	 * {@link IllegalArgumentException} will be thrown
	 * 
	 * @param tableName
	 *            the table name where the field we are looking for resides.
	 * @param fieldName
	 *            the field name in the database that we want to retrieve.
	 */
	public void addSelectField(String tableName, String fieldName) {

		// check to insure arguments are not null.
		if (tableName == null || fieldName == null) {
			throw new IllegalArgumentException("Arguments must be instantiated");

			// check to insure arguments are not empty.
		} else if (tableName.trim().isEmpty() || fieldName.trim().isEmpty()) {
			throw new IllegalArgumentException("Arguments must not be empty.");
		}

		// if we've got clean input, then we add this into a DbField and add
		// it to our list of FieldNames.
		DbField field = new DbField(tableName, fieldName);
		dbFields.add(field);
	}

	/**
	 * Adds a new select field. The select field is the <table name>.<field
	 * name> section that appears in after the "SELECT" keyword. Therefore,
	 * calling addSelectField with "tableA", "fieldA" and "test" would yeild a
	 * select query that would have "SELECT tableA.fieldA test, ... FROM tableA
	 * ...". Note that the table will automatically appear in the select query.
	 * Please note that all parameters must be instantiated and none empty
	 * otherwise an {@link IllegalArgumentException} will be thrown.
	 * 
	 * @param tableName
	 *            the table name where the field we are looking for resides.
	 * @param fieldName
	 *            the field name in the database that we want to retrieve.
	 * @param alias
	 *            the name of the end result of the query for <table name>.<field
	 *            name>
	 */
	public void addSelectField(String tableName, String fieldName, String alias) {

		// check to insure arguments are not null.
		if (tableName == null || fieldName == null || alias == null) {
			throw new IllegalArgumentException("Arguments must be instantiated");

			// check to insure arguments are not empty.
		} else if (tableName.trim().isEmpty() || fieldName.trim().isEmpty()
				|| alias.trim().isEmpty()) {
			throw new IllegalArgumentException("Arguments must not be empty.");
		}

		// if we've got clean input, then we add this into a DbField and add
		// it to our list of FieldNames.
		DbField field = new DbField(tableName, fieldName);
		dbFields.add(field);
		fieldToAlias.put(field, alias);
	}

	/**
	 * Adds a new filter to the SQL statement. The filter (the WHERE clause)
	 * will be appended to any existing conditions. Therefore, if the addWhere
	 * recieves "tableA", "fieldA", "=" and "val", then the SQL query that it
	 * will yield will contain "... WHERE ... tableA.fieldA = val ...". Note
	 * that the operator must be a regular SQL operator otherwise a
	 * {@link RuntimeException} will be thrown.
	 * 
	 * @param tableName
	 *            the table name associated with the field.
	 * @param fieldName
	 *            the field which we will use as a filter.
	 * @param value
	 *            the value which we want to field to have.
	 * @param operator
	 *            the operator between the field name and the value.
	 */
	public void addWhere(String tableName, String fieldName, String value,
			String operator) {
		super.addWhere(tableName, fieldName, value, operator);
	}

	public void addWhere(Key dbKeys) {
		super.addWhere(dbKeys);
	}

	public void addWhere(DbFilterGroup group) {
		super.addWhere(group);
	}

	public PreparedStatement getPreparedStatement() throws SQLException {

		select = null;

		// if the required information is null, we return null.
		if (isEmpty()) {
			return select;
		}

		String sql = "SELECT";

		// if we only have tables, that means that we want to grab everything
		// from those tables.
		if (dbFields.isEmpty() && dbFilters.isEmpty() && !tables.isEmpty()) {
			sql += " *";
		} else {

			// go through all the fields and append the appropriate <table
			// name>.<field name> to the SQL query.
			Iterator<DbField> fieldsIter = dbFields.iterator();
			int index = 0;
			while (fieldsIter.hasNext()) {

				// grab the table name and it's associated field names.
				DbField field = fieldsIter.next();

				// add the <table name>.<field name>
				sql += " " + field.getTable() + "." + field.getField();

				// if we have an alias, then we need to append it to the SQL as
				// well.
				if (fieldToAlias.containsKey(field)) {
					sql += " " + fieldToAlias.get(field);
				}

				// if we're not in the last section, add a comma
				if (index != dbFields.size() - 1) {
					sql += ",";
				}
				index++;
			}
		}

		// create the FROM section.
		sql += " FROM";

		// grab the table names from the other arrays and combine them with the
		// tables we collected.
		Set<String> tableNames = super.getTableNames();
		tableNames.addAll(tables);
		Iterator<String> tables = tableNames.iterator();
		int index = 0;
		while (tables.hasNext()) {
			String tableName = tables.next();
			sql += " " + tableName;

			// if we're not in the last section, add a comma
			if (index != tableNames.size() - 1) {
				sql += ",";
			}
			index++;
		}

		// create the where unknows if the keys aren't null.
		sql += super.createWhereUnknowns(true);

		// if we have any limiter, then we need to add it as well.
		if (super.getLimit() != null) {
			sql += super.getLimit().getFormattedSQL();
		}

		// prepare the statement
		select = connection.prepareStatement(sql);

		int fillIndex = 1;

		// fill in the keys if their not null.
		super.fillUnknowns(select, dbFilters.getFlatSet(), fillIndex);

		return select;
	}

	/**
	 * Executes the SQL code that this SQLSelect contains and returns the
	 * {@link ResultSet}. The effect is the same calling getPreparedStatement
	 * and executing the returned {@link PreparedStatement}. If this SQLSelect
	 * object has not had any SQL added to it, an {@link IllegalStateException}
	 * will be thrown. Any SQL exceptions that occur will also be thrown.
	 * 
	 * @return the result of executing this SQLSelect's SQL.
	 * @throws SQLException
	 *             any SQL execeptions that occur.
	 */
	public ResultSet execute() throws SQLException {

		select = getPreparedStatement();
		if (select == null) {
			throw new IllegalStateException("No SQL to execute");
		}

		// get the result.
		ResultSet result = select.executeQuery();

		// log that this information was accessed.
		Logger logger = Logger.getLogger(SQLSelect.class);

		// TODO: add the actual user
		logger.debug("Following SQLSelect query executed: " + select.toString());

		return result;
	}

	/**
	 * Closes the {@link PreparedStatement} contained in this SQLSelect object.
	 * Do this after you've finished using the {@link ResultSet} returned by
	 * executing the object. Throws any SQLExceptions that occur.
	 * 
	 * @throws SQLException
	 *             if a SQLException occurs, we throw it.
	 * 
	 */
	public void close() throws SQLException {
		select.close();
	}

	/**
	 * Returns true if this SQLSelect doesn't have any field names to pull or
	 * any filters to apply and no table names to pull information from. False
	 * otherwise.
	 * 
	 * @return true if this SQLSelect doesn't have any to extract and false
	 *         otherwise.
	 */
	public boolean isEmpty() {

		boolean empty = false;
		if (dbFields == null
				|| dbFilters == null
				|| (dbFields.isEmpty() && dbFilters.isEmpty() && tables
						.isEmpty())) {
			empty = true;
		}

		return empty;
	}
}